<?php
session_start();
require("../includes/dbInfo.php");

if( $_POST['export'] ) {
    buildFile();
}

/** buildFile
    Formats the swimperformance team, accounts, and events page accordingly into a csv file
    CSV Format:
    Name, Gender, Birthdate, SwimTime, Place, EventName, AgeGroup, EventType, EventLength, MeetName, MeetLocation, MeetDate
*/
function buildFile() {
     $csv_terminated = "\n";
    $csv_separator = ",";
    $csv_enclosed = '"';
    $csv_escaped = "\\";
    $sql_query = "select * from swimPerformance";
    $filename = "exportSwimmerData.csv";
 
    // Gets the data from the database
    $result = mysql_query($sql_query);
 
    // Format the data
    while ($row = mysql_fetch_assoc($result)){
        $sql = mysql_query("SELECT * FROM accounts WHERE userID = '$row[swimmerID]'");
        $user = mysql_fetch_assoc($sql);
        
        $sql = mysql_query("SELECT * FROM ls_events WHERE eventID = '$row[eventID]'");
        $event = mysql_fetch_assoc($sql);
        
        $sql = mysql_query("SELECT * FROM meets WHERE meetID = '$event[meetID]'");
        $meet = mysql_fetch_assoc($sql);
    
        $schema_insert = '';
        $schema_insert .= $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $user[name]) . $csv_enclosed . $csv_separator;
        $schema_insert .= $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $user[gender]) . $csv_enclosed . $csv_separator;
        $schema_insert .= $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $user[birthday]) . $csv_enclosed . $csv_separator;
        $schema_insert .= $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $row[time]) . $csv_enclosed . $csv_separator;
        $schema_insert .= $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $row[place]) . $csv_enclosed . $csv_separator;
        $schema_insert .= $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $event[name]) . $csv_enclosed . $csv_separator;
        $schema_insert .= $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $event[ageGroup]) . $csv_enclosed . $csv_separator;
        $schema_insert .= $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $event[eventType]) . $csv_enclosed . $csv_separator;
        $schema_insert .= $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $event[eventLength]) . $csv_enclosed . $csv_separator;
        $schema_insert .= $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $meet[name]) . $csv_enclosed . $csv_separator;
        $schema_insert .= $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $meet[location]) . $csv_enclosed . $csv_separator;
        $schema_insert .= $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $meet[date]) . $csv_enclosed . $csv_separator;
            
        $out .= $schema_insert;
        $out .= $csv_terminated;
    } // end while
 
    header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    header("Content-Length: " . strlen($out));
    // Output to browser with appropriate mime type, you choose ;)
    header("Content-type: text/x-csv");
    //header("Content-type: text/csv");
    //header("Content-type: application/csv");
    header("Content-Disposition: attachment; filename=$filename");
    echo $out;
    exit;
}

/* 
ProcessLines:
given the contents of a file, process the information and put into the proper tables.

Rules:
If a swimmer cannot be found we do not put them into the performance hostory table. Report an error
If an event cannot be found, then an event is created. 
If the meet cannot be found, then a meet is created.

Assumed CSV Format:
Name, Gender, Birthdate, SwimTime, Place, EventName, AgeGroup, EventType, EventLength, MeetName, MeetLocation, MeetDate
*/
function processLines($csvcontent) {
    $fieldseparator = ",";
    $lines = 0;
    
    // split each line
    foreach(split("\n",$csvcontent) as $line) {
        $lines++;
        if( $line == "" ) {
            continue;
        }
        
        $linearray = explode($fieldseparator, $line);
        
        // for more readable data - remove quotes from around values
        $name         = str_replace("\"","", $linearray[0]);
        $gender       = str_replace("\"","", $linearray[1]);
        $birthdate    = str_replace("\"","", $linearray[2]);
        $swimTime     = str_replace("\"","", $linearray[3]);
        $place        = str_replace("\"","", $linearray[4]);
        $eventName    = str_replace("\"","", $linearray[5]);
        $ageGroup     = str_replace("\"","", $linearray[6]);
        $eventType    = str_replace("\"","", $linearray[7]);
        $eventLength  = str_replace("\"","", $linearray[8]);
        $meetName     = str_replace("\"","", $linearray[9]);
        $meetLocation = str_replace("\"","", $linearray[10]);
        $meetDate     = str_replace("\"","", $linearray[11]);
        // now the real work begines
        
        // check if the swimmer exists - use LIKE to ensure case insensitiviy
        $sql = "SELECT * FROM accounts WHERE name = '$name' AND birthday = '$birthdate'";
        $sql = mysql_query($sql) or die(mysql_error());
        if( mysql_num_rows($sql) == 0 ) {
            echo "Could not find swimmer $name ($birthdate) at line: $lines.<br><br>";
            // next swimmer
            continue;
        } 
        
        $swimmer = mysql_fetch_assoc($sql);
        $swimmer = $swimmer[userID];
        
        //find the event and insert data to the performance history table
        $sql = mysql_query("SELECT * FROM ls_events WHERE name = '$eventName' AND ageGroup = '$ageGroup' AND gender = '$gender'
                            AND eventType = '$eventType' AND eventLength = '$eventLength'") or die(mysql_error());
        if( mysql_num_rows($sql) == 0 ) {
            echo "Could not find evemt $eventName ($eventType; $eventLength; for $gender in $ageGroup) at line: $lines. Creating event... <br>";
            // goto 75 below
        }
        else {
            $event = mysql_fetch_assoc($sql);
            $event = $event[eventID];
        
            $sql = "INSERT INTO swimPerformance (swimmerID, eventID, time, place) VALUES ('$swimmer', '$event', '$swimTime', '$place')";
            $sql = mysql_query($sql);
            if( mysql_error() ) {        
                echo "<br>Error inserting data: " . mysql_error();
            }
            // we are done with this row
            continue;
        }
        
        // if we don't have an event before we create it, we to need find or make a meet for it
        $meetID = 0;
        $sql = mysql_query("SELECT * FROM meets WHERE name = '$meetName' AND location = '$meetLocation' AND date = '$meetDate'")
        or die(mysql_error());
        if( mysql_num_rows($sql) == 0 ) {
            echo "Could not find meet for event. Creating meet... <br>";
            
            // first create the iZeit event
            $id = $meetDate . time();
            $zDate = date("U", $meetDate);
            $sql = mysql_query("INSERT INTO events (id, category, eventstart, eventend, title, linkgoogle, allday, status, author, comments, icon) 
                    VALUES ('$id', 'psdfsgtr', '$zDate', '$zDate', '$meetName', 0, 1, 'Public', 'admin', '0', '-1')") or die(mysql_error());
            
            // now create the meet
            $sql = mysql_query("INSERT INTO meets (meetID, location, date, name, price, izeitID) 
            VALUES (NULL, '$meetLocation', '$meetDate', '$meetName', '0', '$id')") or die(mysql_error());
            $meetID = mysql_insert_id();
            echo "Meet successfully created.<br>";
        }        
        else {
            // we have a meet for the event
            $meet = mysql_fetch_assoc($sql);
            $meetID = $meet[meetID];
        }
        
        // now that we for sure have a meet id, create the event
         $sql = mysql_query("INSERT INTO ls_events (eventID, name, ageGroup, gender, eventType, eventLength, meetID, eventDate) 
        VALUES (NULL, '$eventName', '$ageGroup', '$gender', '$eventType', '$eventLength', '$meetID', '$meetDate')") or die(mysql_error());
        $eventID = mysql_insert_id();
        echo "Event successfully created.<br><br>";
        
        // now add to swim performance!!
        $sql = "INSERT INTO swimPerformance (swimmerID, eventID, time, place) VALUES ('$swimmer', '$eventID', '$swimTime', '$place')";
        $sql = mysql_query($sql);
        if( mysql_error() ) {        
            echo "Error inserting data: " . mysql_error();
        }
    }
    
    echo "<br><br><b>Parsing file complete. Read: $lines lines</b><br>";
}
?>